package cn.edu.gzgs.service.impl;

import cn.edu.gzgs.dto.PageQueryDTO;
import cn.edu.gzgs.dto.RosterImportDTO;
import cn.edu.gzgs.dto.UserDTO;
import cn.edu.gzgs.dto.UserRoleAssignDTO;
import cn.edu.gzgs.dto.UserStatusUpdateDTO;
import cn.edu.gzgs.entity.*;
import cn.edu.gzgs.exception.BusinessException;
import cn.edu.gzgs.mapper.*;
import cn.edu.gzgs.service.UserService;
import cn.edu.gzgs.vo.BatchOperationResultVO;
import cn.edu.gzgs.vo.ImportResultVO;
import cn.edu.gzgs.vo.UserVO;
import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.context.AnalysisContext;
import com.alibaba.excel.event.AnalysisEventListener;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import cn.hutool.core.io.IoUtil;
import cn.hutool.crypto.SecureUtil;
import org.springframework.security.core.context.SecurityContextHolder;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import com.baomidou.mybatisplus.extension.service.impl.ServiceImpl;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.BeanUtils;
import org.springframework.beans.factory.annotation.Autowired;
import cn.edu.gzgs.mapper.RosterFileFingerprintMapper;
import cn.edu.gzgs.mapper.RosterImportLogMapper;
import cn.edu.gzgs.entity.RosterFileFingerprint;
import cn.edu.gzgs.entity.RosterImportLog;
import org.springframework.security.crypto.password.PasswordEncoder;
import org.springframework.stereotype.Service;
import java.time.LocalDateTime;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.io.InputStream;
import java.time.LocalDate;
import java.time.format.DateTimeFormatter;
import java.time.format.DateTimeParseException;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.Objects;
import java.util.Set;
import java.util.function.Function;
import java.util.stream.Collectors;
import java.util.stream.Stream;
import java.util.HashMap;

/**
 * 用户/教师管理服务实现类
 *
 * @author Zyf
 */
@Service
@Slf4j
public class UserServiceImpl extends ServiceImpl<UserMapper, User> implements UserService {

    @Autowired
    private DeptMapper deptMapper;
    @Autowired
    private TitleMapper titleMapper;
    @Autowired
    private PositionMapper positionMapper;
    @Autowired
    private DutyMapper dutyMapper;
    @Autowired
    private EducationMapper educationMapper;
    @Autowired
    private UserMapper userMapper;
    @Autowired
    private UserTitleMapper userTitleMapper;
    @Autowired
    private UserPositionMapper userPositionMapper;
    @Autowired
    private UserDutyMapper userDutyMapper;
    @Autowired
    private UserEducationMapper userEducationMapper;
    @Autowired
    private UserRoleMapper userRoleMapper;
    @Autowired
    private RoleMapper roleMapper;
    @Autowired
    private PasswordEncoder passwordEncoder;

    @Autowired
    private RosterFileFingerprintMapper fingerprintMapper;
    @Autowired
    private RosterImportLogMapper importLogMapper;

    private static final String DEFAULT_PASSWORD = "123456";
    private static final int BATCH_SIZE = 500;

    @Override
    public Page<UserVO> getUserPage(PageQueryDTO pageQueryDTO) {
        Page<User> page = new Page<>(pageQueryDTO.getPage(), pageQueryDTO.getPageSize());
        LambdaQueryWrapper<User> queryWrapper = new LambdaQueryWrapper<>();

        // 条件搜索
        if (StringUtils.hasText(pageQueryDTO.getKeyword())) {
            queryWrapper.like(User::getName, pageQueryDTO.getKeyword())
                    .or().like(User::getUserNo, pageQueryDTO.getKeyword());
        }

        // 1) 先分页查询 User 基表
        page = userMapper.selectPage(page, queryWrapper);
        List<User> users = page.getRecords();
        if (CollectionUtils.isEmpty(users)) {
            return new Page<>();
        }

        // 2) 收集所有需要的主键集合
        List<Long> userIds = users.stream().map(User::getId).toList();
        List<Long> deptIds = users.stream()
                                  .filter(u -> u.getDeptId() != null)
                                  .map(User::getDeptId).toList();

        // 3) 一次性批量查询所有关联数据
        // 3.1 部门
        Map<Long, Dept> deptMap = deptIds.isEmpty() ? Collections.emptyMap() :
                deptMapper.selectBatchIds(deptIds).stream()
                        .collect(Collectors.toMap(Dept::getId, Function.identity()));

        // 3.2 角色关联
        List<UserRole> userRoleList = userRoleMapper.selectList(new LambdaQueryWrapper<UserRole>().in(UserRole::getUserId, userIds));
        Map<Long, List<Long>> userRoleIdsMap = userRoleList.stream()
                .collect(Collectors.groupingBy(UserRole::getUserId, Collectors.mapping(UserRole::getRoleId, Collectors.toList())));
        Set<Long> allRoleIds = userRoleIdsMap.values().stream().flatMap(List::stream).collect(Collectors.toSet());
        Map<Long, Role> roleMap = allRoleIds.isEmpty() ? Collections.emptyMap() :
                roleMapper.selectBatchIds(allRoleIds).stream().collect(Collectors.toMap(Role::getId, Function.identity()));

        // 3.3 职称关联
        List<UserTitle> userTitleList = userTitleMapper.selectList(new LambdaQueryWrapper<UserTitle>().in(UserTitle::getUserId, userIds));
        Map<Long, List<Long>> userTitleIdsMap = userTitleList.stream()
                .collect(Collectors.groupingBy(UserTitle::getUserId, Collectors.mapping(UserTitle::getTitleId, Collectors.toList())));
        Set<Long> allTitleIds = userTitleIdsMap.values().stream().flatMap(List::stream).collect(Collectors.toSet());
        Map<Long, Title> titleMap = allTitleIds.isEmpty() ? Collections.emptyMap() :
                titleMapper.selectBatchIds(allTitleIds).stream().collect(Collectors.toMap(Title::getId, Function.identity()));

        // 3.4 岗位关联
        List<UserPosition> userPositionList = userPositionMapper.selectList(new LambdaQueryWrapper<UserPosition>().in(UserPosition::getUserId, userIds));
        Map<Long, List<Long>> userPositionIdsMap = userPositionList.stream()
                .collect(Collectors.groupingBy(UserPosition::getUserId, Collectors.mapping(UserPosition::getPositionId, Collectors.toList())));
        Set<Long> allPositionIds = userPositionIdsMap.values().stream().flatMap(List::stream).collect(Collectors.toSet());
        Map<Long, Position> positionMap = allPositionIds.isEmpty() ? Collections.emptyMap() :
                positionMapper.selectBatchIds(allPositionIds).stream().collect(Collectors.toMap(Position::getId, Function.identity()));

        // 3.5 职务关联
        List<UserDuty> userDutyList = userDutyMapper.selectList(new LambdaQueryWrapper<UserDuty>().in(UserDuty::getUserId, userIds));
        Map<Long, List<Long>> userDutyIdsMap = userDutyList.stream()
                .collect(Collectors.groupingBy(UserDuty::getUserId, Collectors.mapping(UserDuty::getDutyId, Collectors.toList())));
        Set<Long> allDutyIds = userDutyIdsMap.values().stream().flatMap(List::stream).collect(Collectors.toSet());
        Map<Long, Duty> dutyMap = allDutyIds.isEmpty() ? Collections.emptyMap() :
                dutyMapper.selectBatchIds(allDutyIds).stream().collect(Collectors.toMap(Duty::getId, Function.identity()));

        // 3.6 学历关联
        List<UserEducation> userEduList = userEducationMapper.selectList(new LambdaQueryWrapper<UserEducation>().in(UserEducation::getUserId, userIds));
        Map<Long, List<Long>> userEduIdsMap = userEduList.stream()
                .collect(Collectors.groupingBy(UserEducation::getUserId, Collectors.mapping(UserEducation::getEducationId, Collectors.toList())));
        Set<Long> allEduIds = userEduIdsMap.values().stream().flatMap(List::stream).collect(Collectors.toSet());
        Map<Long, Education> eduMap = allEduIds.isEmpty() ? Collections.emptyMap() :
                educationMapper.selectBatchIds(allEduIds).stream().collect(Collectors.toMap(Education::getId, Function.identity()));

        // 4) 组装 UserVO 列表
        List<UserVO> userVOList = new ArrayList<>(users.size());
        for (User user : users) {
            UserVO vo = new UserVO();
            BeanUtils.copyProperties(user, vo);

            // 部门
            if (user.getDeptId() != null) {
                vo.setDept(deptMap.get(user.getDeptId()));
            }

            Long uid = user.getId();

            // 角色
            List<Role> roles = userRoleIdsMap.getOrDefault(uid, Collections.emptyList()).stream()
                    .map(roleMap::get)
                    .filter(Objects::nonNull)
                    .toList();
            vo.setRoles(roles);

            // 职称
            List<Title> titles = userTitleIdsMap.getOrDefault(uid, Collections.emptyList()).stream()
                    .map(titleMap::get)
                    .filter(Objects::nonNull)
                    .toList();
            vo.setTitles(titles);

            // 岗位
            List<Position> positions = userPositionIdsMap.getOrDefault(uid, Collections.emptyList()).stream()
                    .map(positionMap::get)
                    .filter(Objects::nonNull)
                    .toList();
            vo.setPositions(positions);

            // 职务
            List<Duty> duties = userDutyIdsMap.getOrDefault(uid, Collections.emptyList()).stream()
                    .map(dutyMap::get)
                    .filter(Objects::nonNull)
                    .toList();
            vo.setDuties(duties);

            // 学历
            List<Education> educations = userEduIdsMap.getOrDefault(uid, Collections.emptyList()).stream()
                    .map(eduMap::get)
                    .filter(Objects::nonNull)
                    .toList();
            vo.setEducations(educations);

            userVOList.add(vo);
        }

        // 5) 构造并返回 Page<UserVO>
        Page<UserVO> voPage = new Page<>();
        BeanUtils.copyProperties(page, voPage, "records");
        voPage.setRecords(userVOList);
        return voPage;
    }

    @Override
    public UserVO getUserById(Long id) {
        User user = userMapper.selectById(id);
        if (user == null) {
            throw new BusinessException("用户不存在");
        }
        return mapUserToUserVO(user);
    }
    
    private UserVO mapUserToUserVO(User user) {
        UserVO userVO = new UserVO();
        BeanUtils.copyProperties(user, userVO);
        
        // Populate department
        if (user.getDeptId() != null) {
            Dept dept = deptMapper.selectById(user.getDeptId());
            userVO.setDept(dept);
        }

        // Populate roles, titles, positions, duties, educations
        userVO.setRoles(getRolesByUserId(user.getId()));
        userVO.setTitles(getTitlesByUserId(user.getId()));
        userVO.setPositions(getPositionsByUserId(user.getId()));
        userVO.setDuties(getDutiesByUserId(user.getId()));
        userVO.setEducations(getEducationsByUserId(user.getId()));

        return userVO;
    }

    @Override
    @Transactional
    public Long saveOrUpdateUser(UserDTO userDTO) {
        // ID为空，执行新增逻辑
        if (userDTO.getId() == null) {
            return this.saveUser(userDTO);
        } else { // ID不为空，执行更新逻辑
            return this.updateUser(userDTO);
        }
    }

    @Transactional
    public boolean deleteUserAndAssociations(Long userId) {
        // 1. 先清除所有关联关系
        clearUserAssociations(userId);
        // 2. 然后逻辑删除用户
        boolean result = this.removeById(userId);
        log.info("已逻辑删除ID为 {} 的教师并清除了其所有关联", userId);
        return result;
    }

    private Long saveUser(UserDTO userDTO) {
        if (userMapper.selectOne(new LambdaQueryWrapper<User>().eq(User::getUserNo, userDTO.getUserNo())) != null) {
            throw new BusinessException("工号已存在");
        }
        log.info("新增教师，工号: {}, 姓名: {}", userDTO.getUserNo(), userDTO.getName());

        User user = new User();
        BeanUtils.copyProperties(userDTO, user);
        
        if (StringUtils.hasText(userDTO.getPassword())) {
            user.setPassword(passwordEncoder.encode(userDTO.getPassword()));
        } else {
            user.setPassword(passwordEncoder.encode(DEFAULT_PASSWORD));
        }

        this.save(user); // 使用 IService 的 save
        Long userId = user.getId();
        updateUserAssociations(userId, userDTO);
        return userId;
    }

    private Long updateUser(UserDTO userDTO) {
        User user = this.getById(userDTO.getId());
        if (user == null) {
            throw new BusinessException("用户不存在");
        }
        log.info("更新教师信息，用户ID: {}, 工号: {}", userDTO.getId(), userDTO.getUserNo());
        
        User existingUser = userMapper.selectOne(new LambdaQueryWrapper<User>().eq(User::getUserNo, userDTO.getUserNo()));
        if(existingUser != null && !existingUser.getId().equals(userDTO.getId())) {
             throw new BusinessException("工号已被其他用户使用");
        }

        BeanUtils.copyProperties(userDTO, user, "password"); // 忽略密码
        
        if (StringUtils.hasText(userDTO.getPassword())) {
            user.setPassword(passwordEncoder.encode(userDTO.getPassword()));
        }

        this.updateById(user);
        Long userId = user.getId();

        clearUserAssociations(userId);
        updateUserAssociations(userId, userDTO);
        return userId;
    }

    @Override
    @Transactional
    public BatchOperationResultVO updateUserStatus(UserStatusUpdateDTO userStatusUpdateDTO) {
        List<Long> userIds = userStatusUpdateDTO.getUserIds();
        Integer status = userStatusUpdateDTO.getStatus();

        if (userIds == null || userIds.isEmpty()) {
            throw new BusinessException("用户ID列表不能为空");
        }
        if (status == null || (status != 0 && status != 1)) {
            throw new BusinessException("无效的状态值，必须为0或1");
        }

        List<BatchOperationResultVO.FailureDetail> failures = new ArrayList<>();
        int successCount = 0;

        List<User> users = userMapper.selectBatchIds(userIds);
        Set<Long> existingUserIds = users.stream().map(User::getId).collect(Collectors.toSet());

        for (Long userId : userIds) {
            if (existingUserIds.contains(userId)) {
                User user = new User();
                user.setId(userId);
                user.setStatus(status);
                userMapper.updateById(user);
                successCount++;
            } else {
                failures.add(new BatchOperationResultVO.FailureDetail(userId, "用户不存在"));
            }
        }

        BatchOperationResultVO result = new BatchOperationResultVO();
        result.setSuccessCount(successCount);
        result.setFailureCount(failures.size());
        result.setFailures(failures);

        log.info("批量更新用户状态完成: 成功 {} 个, 失败 {} 个", successCount, failures.size());
        return result;
    }

    @Override
    @Transactional
    public BatchOperationResultVO assignRolesToUsers(UserRoleAssignDTO userRoleAssignDTO) {
        List<Long> userIds = userRoleAssignDTO.getUserIds();
        List<Long> roleIds = userRoleAssignDTO.getRoleIds();

        if (userIds == null || userIds.isEmpty() || roleIds == null || roleIds.isEmpty()) {
            throw new BusinessException("用户ID列表和角色ID列表不能为空");
        }

        List<BatchOperationResultVO.FailureDetail> failures = new ArrayList<>();
        int successCount = 0;

        // 1. 验证所有用户和角色是否存在
        List<User> users = userMapper.selectBatchIds(userIds);
        Set<Long> existingUserIds = users.stream().map(User::getId).collect(Collectors.toSet());
        List<Role> roles = roleMapper.selectBatchIds(roleIds);
        Set<Long> existingRoleIds = roles.stream().map(Role::getId).collect(Collectors.toSet());

        for (Long userId : userIds) {
            if (!existingUserIds.contains(userId)) {
                failures.add(new BatchOperationResultVO.FailureDetail(userId, "用户不存在"));
                continue;
            }

            List<Long> invalidRoleIds = new ArrayList<>();
            for (Long roleId : roleIds) {
                if (!existingRoleIds.contains(roleId)) {
                    invalidRoleIds.add(roleId);
                }
            }
            if (!invalidRoleIds.isEmpty()) {
                failures.add(new BatchOperationResultVO.FailureDetail(userId, "部分角色不存在: " + invalidRoleIds));
                continue;
            }

            // 2. 准备批量插入
            List<UserRole> userRolesToInsert = new ArrayList<>();
            for (Long roleId : roleIds) {
                // 可以在这里检查是否已存在关联，避免重复插入
                UserRole existing = userRoleMapper.selectOne(new LambdaQueryWrapper<UserRole>()
                        .eq(UserRole::getUserId, userId)
                        .eq(UserRole::getRoleId, roleId));
                if (existing == null) {
                    UserRole userRole = new UserRole();
                    userRole.setUserId(userId);
                    userRole.setRoleId(roleId);
                    userRolesToInsert.add(userRole);
                }
            }

            if (!userRolesToInsert.isEmpty()) {
                userRoleMapper.batchInsert(userRolesToInsert);
            }
            successCount++;
        }

        BatchOperationResultVO result = new BatchOperationResultVO();
        result.setSuccessCount(successCount);
        result.setFailureCount(failures.size());
        result.setFailures(failures);

        log.info("批量分配角色完成: 成功 {} 个用户, 失败 {} 个用户", successCount, failures.size());
        return result;
    }

    @Override
    @Transactional
    public BatchOperationResultVO unassignRolesFromUsers(UserRoleAssignDTO userRoleAssignDTO) {
        List<Long> userIds = userRoleAssignDTO.getUserIds();
        List<Long> roleIds = userRoleAssignDTO.getRoleIds();

        if (userIds == null || userIds.isEmpty() || roleIds == null || roleIds.isEmpty()) {
            throw new BusinessException("用户ID列表和角色ID列表不能为空");
        }
        
        userRoleMapper.delete(new LambdaQueryWrapper<UserRole>()
                .in(UserRole::getUserId, userIds)
                .in(UserRole::getRoleId, roleIds));
        
        BatchOperationResultVO result = new BatchOperationResultVO();
        result.setSuccessCount(userIds.size());
        result.setFailureCount(0);
        result.setFailures(Collections.emptyList());

        log.info("批量为 {} 个用户取消了 {} 个角色", userIds.size(), roleIds.size());
        return result;
    }

    // Helper methods for fetching associations, now public for other services to use
    @Override
    public List<Role> getRolesByUserId(Long userId) {
        List<Long> roleIds = userRoleMapper.selectList(new LambdaQueryWrapper<UserRole>().eq(UserRole::getUserId, userId))
                .stream().map(UserRole::getRoleId).collect(Collectors.toList());
        return CollectionUtils.isEmpty(roleIds) ? Collections.emptyList() : roleMapper.selectBatchIds(roleIds);
    }
    
    @Override
    public List<Title> getTitlesByUserId(Long userId) {
        List<Long> titleIds = userTitleMapper.selectList(new LambdaQueryWrapper<UserTitle>().eq(UserTitle::getUserId, userId))
                .stream().map(UserTitle::getTitleId).collect(Collectors.toList());
        return CollectionUtils.isEmpty(titleIds) ? Collections.emptyList() : titleMapper.selectBatchIds(titleIds);
    }
    
    @Override
    public List<Position> getPositionsByUserId(Long userId) {
        List<Long> positionIds = userPositionMapper.selectList(new LambdaQueryWrapper<UserPosition>().eq(UserPosition::getUserId, userId))
                .stream().map(UserPosition::getPositionId).collect(Collectors.toList());
        return CollectionUtils.isEmpty(positionIds) ? Collections.emptyList() : positionMapper.selectBatchIds(positionIds);
    }

    @Override
    public List<Duty> getDutiesByUserId(Long userId) {
        List<Long> dutyIds = userDutyMapper.selectList(new LambdaQueryWrapper<UserDuty>().eq(UserDuty::getUserId, userId))
                .stream().map(UserDuty::getDutyId).collect(Collectors.toList());
        return CollectionUtils.isEmpty(dutyIds) ? Collections.emptyList() : dutyMapper.selectBatchIds(dutyIds);
    }

    @Override
    public List<Education> getEducationsByUserId(Long userId) {
        List<Long> educationIds = userEducationMapper.selectList(new LambdaQueryWrapper<UserEducation>().eq(UserEducation::getUserId, userId))
                .stream().map(UserEducation::getEducationId).collect(Collectors.toList());
        return CollectionUtils.isEmpty(educationIds) ? Collections.emptyList() : educationMapper.selectBatchIds(educationIds);
    }
    
    private void updateUserAssociations(Long userId, UserDTO userDTO) {
        if (!CollectionUtils.isEmpty(userDTO.getRoleIds())) {
            // This part is for single user update, can be optimized to use batchInsert too.
            List<UserRole> userRoles = userDTO.getRoleIds().stream()
                .map(roleId -> new UserRole(userId, roleId)).toList();
            // userRoleMapper does not have batch insert, do it one by one
             userRoles.forEach(userRoleMapper::insert);
        }
        // Titles
        if (!CollectionUtils.isEmpty(userDTO.getTitleIds())) {
            List<UserTitle> userTitles = userDTO.getTitleIds().stream()
                .map(titleId -> new UserTitle(userId, titleId)).toList();
            userTitleMapper.batchInsert(userTitles);
        }
        // Positions
        if (!CollectionUtils.isEmpty(userDTO.getPositionIds())) {
            List<UserPosition> userPositions = userDTO.getPositionIds().stream()
                .map(positionId -> new UserPosition(userId, positionId)).toList();
            userPositionMapper.batchInsert(userPositions);
        }
        // Duties
        if (!CollectionUtils.isEmpty(userDTO.getDutyIds())) {
            List<UserDuty> userDuties = userDTO.getDutyIds().stream()
                .map(dutyId -> new UserDuty(userId, dutyId)).toList();
            userDutyMapper.batchInsert(userDuties);
        }
        // Educations
        if (!CollectionUtils.isEmpty(userDTO.getEducationIds())) {
            List<UserEducation> userEducations = userDTO.getEducationIds().stream()
                .map(educationId -> new UserEducation(userId, educationId)).toList();
            userEducationMapper.batchInsert(userEducations);
        }
    }
    
    private void clearUserAssociations(Long userId) {
        userRoleMapper.delete(new LambdaQueryWrapper<UserRole>().eq(UserRole::getUserId, userId));
        userTitleMapper.delete(new LambdaQueryWrapper<UserTitle>().eq(UserTitle::getUserId, userId));
        userPositionMapper.delete(new LambdaQueryWrapper<UserPosition>().eq(UserPosition::getUserId, userId));
        userDutyMapper.delete(new LambdaQueryWrapper<UserDuty>().eq(UserDuty::getUserId, userId));
        userEducationMapper.delete(new LambdaQueryWrapper<UserEducation>().eq(UserEducation::getUserId, userId));
    }


    @Override
    @Transactional
    public ImportResultVO importRoster(InputStream inputStream, String filename) {
        // 1. 计算文件指纹 MD5
        byte[] fileBytes;
        try {
            fileBytes = IoUtil.readBytes(inputStream);
            // 读取完后重建流供 EasyExcel
            inputStream = IoUtil.toStream(fileBytes);
        } catch (Exception e) {
            throw new BusinessException("读取文件失败: " + e.getMessage());
        }
        String fileHash = SecureUtil.md5().digestHex(fileBytes);
        Long currentUserId = 0L;
        try {
            var auth = SecurityContextHolder.getContext().getAuthentication();
            if (auth != null && auth.isAuthenticated()) {
                String userNo = auth.getName();
                User u = userMapper.selectOne(new LambdaQueryWrapper<User>().eq(User::getUserNo, userNo));
                if (u != null) currentUserId = u.getId();
            }
        } catch (Exception ignored) {}
        // 指纹去重
        if (fingerprintMapper.selectCount(new LambdaQueryWrapper<RosterFileFingerprint>().eq(RosterFileFingerprint::getFileHash, fileHash)) > 0) {
            throw new BusinessException("该文件已导入，请勿重复上传");
        }
        fingerprintMapper.insert(new RosterFileFingerprint(null, fileHash, filename, currentUserId, LocalDateTime.now()));

        // 全局统计器
        final List<String> errors = new ArrayList<>();
        final int[] totalRows = {0};
        final int[] successCount = {0};

        // 全局缓存 Map，用于批量处理时去重、减少数据库查询
        final Map<String, Long> deptMap = new HashMap<>();
        final Map<String, Long> titleMap = new HashMap<>();
        final Map<String, Long> positionMap = new HashMap<>();
        final Map<String, Long> dutyMap = new HashMap<>();
        final Map<String, Long> educationMap = new HashMap<>();

        // 批处理缓存
        final List<RosterImportDTO> batchList = new ArrayList<>(BATCH_SIZE);

        try {
            EasyExcel.read(inputStream, RosterImportDTO.class, new AnalysisEventListener<RosterImportDTO>() {
                @Override
                public void invoke(RosterImportDTO data, AnalysisContext context) {
                    totalRows[0]++;
                    batchList.add(data);
                    if (batchList.size() >= BATCH_SIZE) {
                        processBatch(batchList, deptMap, titleMap, positionMap, dutyMap, educationMap, errors);
                        successCount[0] += batchList.size();
                        batchList.clear();
                    }
                }

                @Override
                public void doAfterAllAnalysed(AnalysisContext context) {
                    if (!batchList.isEmpty()) {
                        processBatch(batchList, deptMap, titleMap, positionMap, dutyMap, educationMap, errors);
                        successCount[0] += batchList.size();
                        batchList.clear();
                    }
                }
            }).sheet().doRead();
        } catch (Exception e) {
            log.error("使用EasyExcel解析文件失败", e);
            throw new BusinessException("文件解析失败，请检查文件格式是否正确。");
        }

        int failureCount = errors.size();
        // successCount 在批处理时统计的是读取到的行，不代表真正插入成功，
        // 这里简单使用 totalRows - failureCount 作为成功数。
        successCount[0] = totalRows[0] - failureCount;

        log.info("文件导入完成. 总行数: {}, 成功: {}, 失败: {}", totalRows[0], successCount[0], failureCount);

        // 写审计日志
        RosterImportLog audit = new RosterImportLog(null, currentUserId, filename, totalRows[0], successCount[0], failureCount,
                errors.isEmpty()? null : String.join(";", errors.subList(0, Math.min(5, errors.size()))), LocalDateTime.now());
        importLogMapper.insert(audit);
        return new ImportResultVO(totalRows[0], successCount[0], failureCount, errors);
    }

    private void processBatch(List<RosterImportDTO> batchList, Map<String, Long> deptMap, Map<String, Long> titleMap, Map<String, Long> positionMap, Map<String, Long> dutyMap, Map<String, Long> educationMap, List<String> errors) {
        Map<String, User> userMapInBatch = processUsers(batchList, deptMap, errors);
        if(userMapInBatch.isEmpty()){
            return;
        }
        processUserTitles(batchList, userMapInBatch, titleMap);
        processUserPositions(batchList, userMapInBatch, positionMap);
        processUserDuties(batchList, userMapInBatch, dutyMap);
        processUserEducations(batchList, userMapInBatch, educationMap);
    }

    private Map<String, Long> processDepts(List<RosterImportDTO> dtoList) {
        List<String> names = dtoList.stream().map(RosterImportDTO::getDepartment).filter(StringUtils::hasText).distinct().toList();
        if (names.isEmpty()) return Map.of();
        Map<String, Dept> existing = deptMapper.selectList(new LambdaQueryWrapper<Dept>().in(Dept::getName, names))
                .stream().collect(Collectors.toMap(Dept::getName, Function.identity()));
        List<Dept> newItems = names.stream()
                .filter(name -> !existing.containsKey(name))
                .map(name -> {
                    Dept dept = new Dept();
                    dept.setName(name);
                    dept.setType(200); // Assuming 200 is a default type for departments
                    return dept;
                }).toList();
        if (!newItems.isEmpty()) {
            newItems.forEach(deptMapper::insert);
            log.info("新增 {} 个部门", newItems.size());
        }
        return deptMapper.selectList(null).stream().collect(Collectors.toMap(Dept::getName, Dept::getId, (o1, o2) -> o1));
    }

    private Map<String, Long> processPositions(List<RosterImportDTO> dtoList) {
        List<String> names = dtoList.stream().map(RosterImportDTO::getPosition).filter(StringUtils::hasText).distinct().toList();
        if (names.isEmpty()) return Map.of();
        Map<String, Position> existing = positionMapper.selectList(new LambdaQueryWrapper<Position>().in(Position::getName, names))
                .stream().collect(Collectors.toMap(Position::getName, Function.identity()));
        List<Position> newItems = names.stream()
                .filter(name -> !existing.containsKey(name))
                .map(name -> {
                    Position p = new Position();
                    p.setName(name);
                    p.setType("教学岗"); // Example default
                    return p;
                }).toList();
        if (!newItems.isEmpty()) {
            newItems.forEach(positionMapper::insert);
            log.info("新增 {} 个岗位", newItems.size());
        }
        return positionMapper.selectList(null).stream().collect(Collectors.toMap(Position::getName, Position::getId, (o1, o2) -> o1));
    }
    
    private Map<String, Long> processDuties(List<RosterImportDTO> dtoList) {
        List<String> names = dtoList.stream().map(RosterImportDTO::getDuty).filter(StringUtils::hasText).distinct().toList();
        if (names.isEmpty()) return Map.of();
        Map<String, Duty> existing = dutyMapper.selectList(new LambdaQueryWrapper<Duty>().in(Duty::getName, names))
                .stream().collect(Collectors.toMap(Duty::getName, Function.identity()));
        List<Duty> newItems = names.stream()
                .filter(name -> !existing.containsKey(name))
                .map(name -> {
                    Duty d = new Duty();
                    d.setName(name);
                    return d;
                }).toList();
        if (!newItems.isEmpty()) {
            newItems.forEach(dutyMapper::insert);
            log.info("新增 {} 个职务", newItems.size());
        }
        return dutyMapper.selectList(null).stream().collect(Collectors.toMap(Duty::getName, Duty::getId, (o1, o2) -> o1));
    }

    private Map<String, Long> processTitles(List<RosterImportDTO> dtoList) {
        List<Title> collegeTitles = dtoList.stream()
                .filter(dto -> StringUtils.hasText(dto.getCollegeTitleName()) && !"无".equals(dto.getCollegeTitleName()))
                .map(dto -> new Title(null, dto.getCollegeTitleName(), dto.getCollegeTitleLevel(), "高校", null, null))
                .distinct().toList();
        List<Title> nonCollegeTitles = dtoList.stream()
                .filter(dto -> StringUtils.hasText(dto.getNonCollegeTitleName()) && !"无".equals(dto.getNonCollegeTitleName()))
                .map(dto -> new Title(null, dto.getNonCollegeTitleName(), dto.getNonCollegeTitleLevel(), "非高校", null, null))
                .distinct().toList();
        
        List<Title> allTitles = Stream.concat(collegeTitles.stream(), nonCollegeTitles.stream()).toList();
        if (allTitles.isEmpty()) return Map.of();
        
        Function<Title, String> compositeKey = t -> t.getName() + "::" + t.getSeries();
        Map<String, Title> existingTitles = titleMapper.selectList(null).stream().collect(Collectors.toMap(compositeKey, Function.identity(), (o1, o2) -> o1));
        
        List<Title> newTitles = allTitles.stream().filter(t -> !existingTitles.containsKey(compositeKey.apply(t))).toList();
        if (!newTitles.isEmpty()) {
            newTitles.forEach(titleMapper::insert);
            log.info("新增 {} 个职称", newTitles.size());
        }
        return titleMapper.selectList(null).stream().collect(Collectors.toMap(compositeKey, Title::getId, (o1, o2) -> o1));
    }

    private Map<String, Long> processEducations(List<RosterImportDTO> dtoList) {
        List<Education> educations = dtoList.stream()
                .map(RosterImportDTO::getHighestEducation).filter(StringUtils::hasText)
                .map(name -> new Education(null, name, "学历", null, null))
                .distinct().toList();
        List<Education> degrees = dtoList.stream()
                .map(RosterImportDTO::getHighestDegree).filter(StringUtils::hasText)
                .map(name -> new Education(null, name, "学位", null, null))
                .distinct().toList();
        
        List<Education> allEducations = Stream.concat(educations.stream(), degrees.stream()).toList();
        if (allEducations.isEmpty()) return Map.of();

        Function<Education, String> compositeKey = e -> e.getName() + "::" + e.getType();
        Map<String, Education> existing = educationMapper.selectList(null).stream().collect(Collectors.toMap(compositeKey, Function.identity(), (o1, o2) -> o1));

        List<Education> newItems = allEducations.stream().filter(e -> !existing.containsKey(compositeKey.apply(e))).toList();
        if (!newItems.isEmpty()) {
            newItems.forEach(educationMapper::insert);
            log.info("新增 {} 个学历/学位", newItems.size());
        }
        return educationMapper.selectList(null).stream().collect(Collectors.toMap(compositeKey, Education::getId, (o1, o2) -> o1));
    }

    private Map<String, User> processUsers(List<RosterImportDTO> dtoList, Map<String, Long> deptMap, List<String> errors) {
        List<String> userNos = dtoList.stream().map(RosterImportDTO::getUserNo).toList();
        Map<String, User> existingUsers = userMapper.selectList(new LambdaQueryWrapper<User>().in(User::getUserNo, userNos))
                .stream().collect(Collectors.toMap(User::getUserNo, Function.identity()));
        
        List<User> newUsers = new ArrayList<>();
        for (RosterImportDTO dto : dtoList) {
            if (existingUsers.containsKey(dto.getUserNo())) {
                errors.add("行 " + (dtoList.indexOf(dto) + 2) + ": 工号 " + dto.getUserNo() + " 已存在，跳过该用户。");
                continue;
            }
            if (!StringUtils.hasText(dto.getUserNo()) || !StringUtils.hasText(dto.getName())) {
                 errors.add("行 " + (dtoList.indexOf(dto) + 2) + ": 工号或姓名为空，跳过该用户。");
                continue;
            }
            User user = new User();
            user.setUserNo(dto.getUserNo());
            user.setName(dto.getName());
            user.setPassword(passwordEncoder.encode(DEFAULT_PASSWORD));
            user.setDeptId(deptMap.get(dto.getDepartment()));
            user.setRemark(dto.getRemark());
            try {
                if (StringUtils.hasText(dto.getEntryDate())) {
                    user.setEntryDate(LocalDate.parse(dto.getEntryDate(), DateTimeFormatter.ofPattern("yyyy-MM-dd")));
                }
            } catch (DateTimeParseException e) {
                 errors.add("行 " + (dtoList.indexOf(dto) + 2) + ": 日期格式错误 (应为 yyyy-MM-dd)，工号: " + dto.getUserNo());
            }
            newUsers.add(user);
        }

        if (!newUsers.isEmpty()) {
            newUsers.forEach(userMapper::insert);
            log.info("批量插入 {} 个新用户", newUsers.size());
        }
        
        // Return a map of newly created users for association processing
        return newUsers.stream().collect(Collectors.toMap(User::getUserNo, Function.identity()));
    }

    private void processUserTitles(List<RosterImportDTO> dtoList, Map<String, User> userMap, Map<String, Long> titleMap) {
        List<UserTitle> batchToInsert = new ArrayList<>();
        for (RosterImportDTO dto : dtoList) {
            User user = userMap.get(dto.getUserNo());
            if (user == null) continue;

            String collegeKey = dto.getCollegeTitleName() + "::高校";
            if (titleMap.containsKey(collegeKey)) {
                batchToInsert.add(new UserTitle(user.getId(), titleMap.get(collegeKey)));
            }
            String nonCollegeKey = dto.getNonCollegeTitleName() + "::非高校";
            if (titleMap.containsKey(nonCollegeKey)) {
                batchToInsert.add(new UserTitle(user.getId(), titleMap.get(nonCollegeKey)));
            }
        }
        if (!batchToInsert.isEmpty()) {
            userTitleMapper.batchInsert(batchToInsert);
        }
    }

    private void processUserPositions(List<RosterImportDTO> dtoList, Map<String, User> userMap, Map<String, Long> positionMap) {
        List<UserPosition> batchToInsert = dtoList.stream()
            .map(dto -> {
                User user = userMap.get(dto.getUserNo());
                Long positionId = positionMap.get(dto.getPosition());
                if (user != null && positionId != null) {
                    return new UserPosition(user.getId(), positionId);
                }
                return null;
            }).filter(Objects::nonNull).toList();
        
        if(!batchToInsert.isEmpty()) {
            userPositionMapper.batchInsert(batchToInsert);
        }
    }
    
    private void processUserDuties(List<RosterImportDTO> dtoList, Map<String, User> userMap, Map<String, Long> dutyMap) {
         List<UserDuty> batchToInsert = dtoList.stream()
            .map(dto -> {
                User user = userMap.get(dto.getUserNo());
                Long dutyId = dutyMap.get(dto.getDuty());
                if (user != null && dutyId != null) {
                    return new UserDuty(user.getId(), dutyId);
                }
                return null;
            }).filter(Objects::nonNull).toList();

        if(!batchToInsert.isEmpty()) {
            userDutyMapper.batchInsert(batchToInsert);
        }
    }

    private void processUserEducations(List<RosterImportDTO> dtoList, Map<String, User> userMap, Map<String, Long> educationMap) {
        List<UserEducation> batchToInsert = new ArrayList<>();
        for (RosterImportDTO dto : dtoList) {
            User user = userMap.get(dto.getUserNo());
            if (user == null) continue;

            String educationKey = dto.getHighestEducation() + "::学历";
            if (educationMap.containsKey(educationKey)) {
                batchToInsert.add(new UserEducation(user.getId(), educationMap.get(educationKey)));
            }
            String degreeKey = dto.getHighestDegree() + "::学位";
            if (educationMap.containsKey(degreeKey)) {
                batchToInsert.add(new UserEducation(user.getId(), educationMap.get(degreeKey)));
            }
        }
        if (!batchToInsert.isEmpty()) {
            userEducationMapper.batchInsert(batchToInsert);
        }
    }
}